স্পার্ক এসকিউএল (Spark SQL) বাস্তব জগতের বড় ডেটাসেটের উপর কার্যকরী ডেটা বিশ্লেষণ ও প্রসেসিং করার জন্য খুবই গুরুত্বপূর্ণ। Complex Joins এবং Subqueries এই বিশ্লেষণের জন্য খুবই কার্যকরী। এদের মাধ্যমে আমরা একাধিক টেবিল বা DataFrame থেকে সম্পর্কিত ডেটা একত্রিত করতে পারি এবং জটিল ডেটা সিলেকশন ও ফিল্টারিং অপারেশন চালাতে পারি। চলুন, কিছু বাস্তব উদাহরণ দেখে, যেখানে Complex Joins এবং Subqueries ব্যবহৃত হয়।
1. Complex Joins: Sales Data Analysis
ধরা যাক, আমাদের কাছে দুটি টেবিল রয়েছে:
- Sales: যেখানে বিক্রয়ের তথ্য রয়েছে।
- Customers: যেখানে গ্রাহকের তথ্য রয়েছে।
এখন, আমাদের কাজ হল, Sales টেবিল থেকে সব বিক্রয়ের তথ্য এবং সংশ্লিষ্ট গ্রাহকের নাম, শহর এবং বয়স দেখতে হবে। এ ক্ষেত্রে INNER JOIN ব্যবহার করে এই দুটি টেবিল একত্রিত করা যেতে পারে।
উদাহরণ:
# Sales টেবিলের ডেটা
sales_data = [("2023-01-01", 101, 500), ("2023-01-02", 102, 1000), ("2023-01-03", 101, 1500)]
sales_columns = ["date", "customer_id", "amount"]
# Customers টেবিলের ডেটা
customers_data = [(101, "John", "New York", 28), (102, "Alice", "Los Angeles", 35)]
customers_columns = ["customer_id", "name", "city", "age"]
# DataFrame তৈরি
sales_df = spark.createDataFrame(sales_data, sales_columns)
customers_df = spark.createDataFrame(customers_data, customers_columns)
# INNER JOIN করা
df_joined = sales_df.join(customers_df, "customer_id", "inner")
df_joined.show()
আউটপুট:
+-----------+----------+------+-------------+-----------+-----------+
| date|customer_id|amount| name| city| age|
+-----------+----------+------+-------------+-----------+-----------+
| 2023-01-01| 101| 500| John| New York| 28|
| 2023-01-02| 102| 1000| Alice| Los Angeles| 35|
+-----------+----------+------+-------------+-----------+-----------+
এখানে, Sales এবং Customers টেবিলের মধ্যে INNER JOIN ব্যবহার করা হয়েছে, যাতে গ্রাহকের বিক্রয়ের পরিমাণ এবং গ্রাহকের তথ্য একত্রিত হয়।
2. LEFT JOIN Example: Order Data Analysis
ধরা যাক, আমাদের কাছে দুটি টেবিল:
- Orders: যেখানে অর্ডারের তথ্য রয়েছে।
- Products: যেখানে পণ্যের তথ্য রয়েছে।
এখানে, আমাদের কাজ হল LEFT JOIN ব্যবহার করে সব অর্ডার এবং প্রাসঙ্গিক পণ্যের নাম দেখানো। যদি কোনো অর্ডারে পণ্য না থাকে, তবে NULL দেখানো হবে।
উদাহরণ:
# Orders টেবিলের ডেটা
orders_data = [(1, "2023-01-01", 101), (2, "2023-01-02", 102), (3, "2023-01-03", None)]
orders_columns = ["order_id", "order_date", "product_id"]
# Products টেবিলের ডেটা
products_data = [(101, "Laptop"), (102, "Smartphone")]
products_columns = ["product_id", "product_name"]
# DataFrame তৈরি
orders_df = spark.createDataFrame(orders_data, orders_columns)
products_df = spark.createDataFrame(products_data, products_columns)
# LEFT JOIN করা
df_left_join = orders_df.join(products_df, "product_id", "left")
df_left_join.show()
আউটপুট:
+---------+----------+---------+------------+
| order_id|order_date|product_id|product_name|
+---------+----------+---------+------------+
| 1|2023-01-01| 101| Laptop|
| 2|2023-01-02| 102| Smartphone|
| 3|2023-01-03| null| null|
+---------+----------+---------+------------+
এখানে, LEFT JOIN ব্যবহার করে Orders এবং Products টেবিলের মধ্যে সম্পর্ক স্থাপন করা হয়েছে, যাতে সব অর্ডার এবং প্রাসঙ্গিক পণ্য বা NULL শো হয়।
3. Subquery Example: Highest Order Amount
ধরা যাক, আমাদের একটি টেবিল Orders রয়েছে এবং আমরা জানতে চাই, যে গ্রাহক সবচেয়ে বেশি অর্ডার পরিমাণ করেছে, তার তথ্য। এটি একটি Subquery ব্যবহার করে পাওয়া যাবে। Subquery ব্যবহার করে, আমরা সর্বোচ্চ অর্ডার পরিমাণ বের করতে পারি এবং তার ভিত্তিতে গ্রাহকের নাম এবং অর্ডারের তারিখ দেখতে পারি।
উদাহরণ:
# Orders টেবিলের ডেটা
orders_data = [(1, "John", 500), (2, "Alice", 1000), (3, "Bob", 2000)]
orders_columns = ["order_id", "customer_name", "order_amount"]
# DataFrame তৈরি
orders_df = spark.createDataFrame(orders_data, orders_columns)
# Subquery ব্যবহার করে সর্বোচ্চ অর্ডার পরিমাণ বের করা
df_subquery = spark.sql("""
SELECT customer_name, order_amount
FROM orders
WHERE order_amount = (SELECT MAX(order_amount) FROM orders)
""")
df_subquery.show()
আউটপুট:
+-------------+-----------+
|customer_name|order_amount|
+-------------+-----------+
| Bob | 2000|
+-------------+-----------+
এখানে, Subquery ব্যবহার করা হয়েছে যাতে Orders টেবিল থেকে সর্বোচ্চ অর্ডার পরিমাণ (MAX(order_amount)) বের করা হয় এবং তারপর ঐ গ্রাহকের নাম এবং অর্ডারের পরিমাণ দেখানো হয়।
4. Subquery in WHERE Clause: Products Purchased Above Average Price
ধরা যাক, আমাদের একটি Products টেবিল রয়েছে এবং আমরা জানতে চাই, কোন পণ্যগুলো গড় দাম থেকে বেশি দামে বিক্রি হয়েছে। এর জন্য Subquery in WHERE Clause ব্যবহার করা হবে।
উদাহরণ:
# Products টেবিলের ডেটা
products_data = [(101, "Laptop", 1000), (102, "Smartphone", 700), (103, "Tablet", 800)]
products_columns = ["product_id", "product_name", "price"]
# DataFrame তৈরি
products_df = spark.createDataFrame(products_data, products_columns)
# Subquery in WHERE Clause ব্যবহার
df_subquery_where = spark.sql("""
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
""")
df_subquery_where.show()
আউটপুট:
+-----------+-----+
|product_name|price|
+-----------+-----+
| Laptop| 1000|
+-----------+-----+
এখানে, Subquery in WHERE Clause ব্যবহার করে গড় মূল্য (AVG(price)) বের করা হয়েছে এবং তার চেয়ে বেশি দামে বিক্রি হওয়া পণ্য দেখানো হয়েছে।
সারাংশ
Complex Joins এবং Subqueries ব্যবহার করে Spark SQL-এ জটিল ডেটা বিশ্লেষণ করা যায়। Joins বিভিন্ন টেবিলের মধ্যে সম্পর্ক স্থাপন করতে সাহায্য করে, যেখানে Subqueries ব্যবহার করে আরও সুনির্দিষ্ট এবং জটিল কুয়েরি তৈরি করা সম্ভব। বাস্তব জীবনে, Joins এবং Subqueries ব্যবহার করে যেমন বিক্রয় ডেটা, অর্ডার বিশ্লেষণ, পণ্য বিশ্লেষণ এবং গ্রাহক সম্পর্কিত তথ্য সংগ্রহ করা যায়, তেমনি এটি ডেটার ওপর আরও গভীর বিশ্লেষণ করতে সহায়তা করে।
Read more
